package cn.soft863.bike.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import cn.soft863.bike.dao.IABikeDao;
import cn.soft863.bike.pojo.ABike;
import cn.soft863.common.util.DbUtil;
import cn.soft863.common.util.PageInfoUtil;

/**
 * Dao接口的实现类
 * @author 902-B-Book
 *
 */
public class ABikeDaoImpl implements IABikeDao
{
	/**
	 * 将数据库中取出的记录一一的赋值给java的pojo对象
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private ABike proccedPojo(ResultSet rs) throws SQLException
	{
		ABike bike = new ABike() ; 
		bike.setId(rs.getInt("id"));
		bike.setCreateId(rs.getInt("createId"));
		bike.setUpdateId(rs.getInt("updateId"));
		bike.setCateId(rs.getInt("cateId"));
		bike.setName(rs.getString("name"));
		bike.setPassword(rs.getString("password"));
		bike.setPrice(rs.getDouble("price"));
		bike.setRepairCount(rs.getInt("repairCount"));
		bike.setStatus(rs.getByte("status"));
		bike.setCreateTime(rs.getTimestamp("createTime"));
		bike.setUpdateTime(rs.getTimestamp("updateTime"));
		bike.setPubTime(rs.getTimestamp("pubTime"));
		return bike;
	}
	
	@Override
	public int saveOneDao(ABike bike)
	{
		String sql = "INSERT INTO a_bike (createId, updateId, cateId, name, password, price, repairCount, status, createTime, updateTime, pubTime) VALUES "
				+ "	(?,?,?,?,?,?,?,?,?,?,?)"; 
		/* 创建一个链接对象 */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		int res = 0 ; 
		/* 创建一个语句对象 */
		try
		{
			ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			/* 设置占位符 */
			ps.setObject(1, bike.getCreateId());
			ps.setObject(2, bike.getUpdateId());
			ps.setObject(3, bike.getCateId());
			ps.setObject(4, bike.getName());
			ps.setObject(5, bike.getPassword());
			ps.setObject(6, bike.getPrice());
			ps.setObject(7, bike.getRepairCount());
			ps.setObject(8, bike.getStatus());
			ps.setObject(9, bike.getCreateTime());
			ps.setObject(10, bike.getUpdateTime());
			ps.setObject(11, bike.getPubTime());
			res = ps.executeUpdate();
			/* res表示此条sql语句影响条数
			 * 需要返回主键
			 *  */
			rs = ps.getGeneratedKeys();
			/* 肯定有结果,而且只有一条 */
			if(rs.next())
			{
				/* 主键赋值 */
				bike.setId(rs.getInt(1));
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* 关闭链接 */
			DbUtil.closeAll(rs, ps, con);
		}
		return res;
	}
	
	/**
	 * 更新一条记录,是按照id更新的.
	 */
	@Override
	public int updateOneDao(ABike bike)
	{
		String sql = "UPDATE `a_bike` SET `createId`=?, `updateId`=?, `cateId`=?, `name`=?, `password`=?, "
				+ "`price`=?, `repairCount`=?, `status`=?, `createTime`=?, "
				+ "`updateTime`=?, `pubTime`=? WHERE (`id`=?)"; 
		/* 创建一个链接对象 */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		int res = 0 ; 
		/* 创建一个语句对象 */
		try
		{
			ps = con.prepareStatement(sql);
			/* 设置占位符 */
			ps.setObject(1, bike.getCreateId());
			ps.setObject(2, bike.getUpdateId());
			ps.setObject(3, bike.getCateId());
			ps.setObject(4, bike.getName());
			ps.setObject(5, bike.getPassword());
			ps.setObject(6, bike.getPrice());
			ps.setObject(7, bike.getRepairCount());
			ps.setObject(8, bike.getStatus());
			ps.setObject(9, bike.getCreateTime());
			ps.setObject(10, bike.getUpdateTime());
			ps.setObject(11, bike.getPubTime());
			ps.setObject(12, bike.getId());
			res = ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* 关闭链接 */
			DbUtil.closeAll(rs, ps, con);
		}
		return res;
	}

	/**
	 * 既要支持分页,也要支持不分页
	 * 
	 * 查询分页的时候应该先查询总条数,然后再分页
	 */
	@Override
	public List<ABike> findCondListDao(PageInfoUtil pageInfoUtil,Map<String, Object> condMap)
	{
		List<Object> paramList = new ArrayList<Object>();
		/**
		 * 如果不分页,sql语句里面木有?
		 * 如果分页,sql语句里面有?
		 * 
		 * 在给?设置值的时候只能在ps生成以后才可以.有的时候有问可以设置,有的时候木有?不能设置
		 * 咋办呢??的下标值.
		 */
		String sql = "select * from a_bike where 1 = 1 "; 
		
		/* 要拼装sql语句(带条件的)
		 * 
Select * from t where email like ? or phone like ?
Select * from t where status = ? 
Select * from t where createTime >= ? and createTime < ?
Select * from t where (email like ? or phone like ?) and status = ? 
Select * from t where status = ? and (createTime >= ? and createTime < ?)
Select * from t where (email like ? or phone like ? ) and status = ? and (createTime >= ? and createTime < ? )
		 *  */
		/* 按照关键字查询 
		 * 键:是为了区分不同的搜索条件
		 * 值:就是搜索条件
		 * */
		if(condMap.get("keyword") != null && !"".equalsIgnoreCase(condMap.get("keyword") + ""))
		{
			sql += " and (name like ?) ";
			paramList.add("%" + condMap.get("keyword") + "%");
			/*
			 * select * from a_bike email like ? or phone like ?
			 *  */
		} 
		/* 按照状态搜索 */
		if(condMap.get("status") != null && !"".equalsIgnoreCase(condMap.get("status") + ""))
		{
			sql += " and status = ? ";
			paramList.add(condMap.get("status"));
			/*
			 * select * from a_bike status = ?
			 *  */
		} 
		/* 按照时间搜索 */
		if(condMap.get("stDate") != null && !"".equalsIgnoreCase(condMap.get("edDate") + ""))
		{
			sql += " and (pubTime >= ? and pubTime < ? )";
			paramList.add(condMap.get("stDate"));
			paramList.add(condMap.get("edDate"));
			/*
			 * select * from a_bike status = ?
			 *  */
		}
		/*
		 * select * from a_bike where 1 = 1 and email like ? or phone like ?
		 * select * from a_bike where 1 = 1 and status = ?
		 * select * from a_bike where 1 =1  and (email like ? or phone like ?) and  status = ?
		 * 
		 * */
		
		/* 创建一个链接对象 */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		List<ABike> bikeList = new ArrayList();
		int res = 0 ; 
		/* 创建一个语句对象 */
		try
		{
			if(pageInfoUtil != null)
			{
				/*----------------查询总条数------------------*/
				/* 原始的SQL语句:select * from t where (email like ? and trueName like ?) and status = ? and createTime >= ? and createTime < ? 
				 * 计算总记录数:select count(*) from t where (email like ? and trueName like ?) and status = ? and createTime >= ? and createTime < ?
				 * 分页:select * from t where (email like ? and trueName like ?) and status = ? and createTime >= ? and createTime < ? limit ? , ? 
				 *  */
				System.out.println("-----原始的SQL语句-----" + sql);
				String fromIndex = "from" ;
				String countSQL = "select count(*) " + sql.substring(sql.indexOf(fromIndex) , sql.length()); 
				System.out.println("-----countSQL-----" + countSQL);
				ps = con.prepareStatement(countSQL);
				/* 专门解决?的顺序和是否有?的问题?(占位符) */
				int count = 1 ; 
				for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
				{
					Object obj = (Object) iterator.next();
					ps.setObject(count, obj);
					count ++ ; 
				}
				rs = ps.executeQuery();
				/* 凡是count(*)查询的结果只有一条 */
				if(rs.next())
				{
					int totalRecord = rs.getInt(1);
					/* 分页的工具类天天盼着呢 */
					pageInfoUtil.setTotalRecord(totalRecord);
				}
				
				/*----------------分页查询数据------------------*/
				/* 使用的是limit
				 * ?:起始条数
				 * ?:往后查询多少条
				 *  */
				sql += " limit ? , ? ";
				paramList.add(pageInfoUtil.getCurrRecord());
				paramList.add(pageInfoUtil.getPageSize());
				System.out.println("-----分页取数据的SQL语句-----" + sql);
				ps.close();
				rs.close();
			}
			
			ps = con.prepareStatement(sql);
			/* 专门解决?的顺序和是否有?的问题?(占位符) */
			int count = 1 ; 
			for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
			{
				Object obj = (Object) iterator.next();
				ps.setObject(count, obj);
				count ++ ; 
			}
			rs = ps.executeQuery() ;
			/* sql返回的结果有几条
			 *  */
			while(rs.next())
			{
				ABike bike = proccedPojo(rs);
				
				/* 将数据库查询出来的记录直接放到集合中 */
				bikeList.add(bike);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* 关闭链接 */
			DbUtil.closeAll(rs, ps, con);
		}
		return bikeList;
	}

	@Override
	public ABike findOneDao(Map<String, Object> condMap)
	{
		List<Object> paramList = new ArrayList<Object>();
		/**
		 * 如果不分页,sql语句里面木有?
		 * 如果分页,sql语句里面有?
		 * 
		 * 在给?设置值的时候只能在ps生成以后才可以.有的时候有问可以设置,有的时候木有?不能设置
		 * 咋办呢??的下标值.
		 */
		String sql = "select * from a_bike where 1 = 1 "; 
		
		/* 按照id查询
		 * dao中取,在使用调用的时候放
		 * */
		if(condMap.get("id") != null && !"".equalsIgnoreCase(condMap.get("id") + ""))
		{
			sql += " and id = ? ";
			paramList.add(condMap.get("id"));
		}else if(condMap.get("email") != null && !"".equalsIgnoreCase(condMap.get("email") + ""))
		{
			/* 按照email查询
			 * dao中取,在使用调用的时候放
			 * */
			sql += " and email = ? ";
			paramList.add(condMap.get("email"));
		}
		
		/* 创建一个链接对象 */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		List<ABike> bikeList = new ArrayList();
		int res = 0 ; 
		/* 创建一个语句对象 */
		try
		{
			System.out.println("sql---------" + sql);
			ps = con.prepareStatement(sql);
			/* 专门解决?的顺序和是否有?的问题?(占位符) */
			int count = 1 ; 
			for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
			{
				Object obj = (Object) iterator.next();
				ps.setObject(count, obj);
				count ++ ; 
			}
			rs = ps.executeQuery() ;
			/* sql返回的结果有几条
			 * 通过id查询的时候始终只有一条
			 *  */
			if(rs.next())
			{
				/*
				 * 将数据库里面查询出来的信息一一的赋值给java中的POJO
				 *  */
				ABike bike = proccedPojo(rs);
				return bike ; 
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* 关闭链接 */
			DbUtil.closeAll(rs, ps, con);
		}
		return null;
	}
}
